//202102104001魏晓宇
package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.Teacher;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class TeacherDao {
    private static final TeacherDao teacherDao = new TeacherDao();
    private static Collection<Teacher> teachers;

    private TeacherDao() {
    }

    public static TeacherDao getInstance() {
        return teacherDao;
    }

    /**
     * @param connection    数据库连接对象
     * @param conditionList 查询条件
     * @param pagination    分页对象
     * @return
     * @throws SQLException
     */
    public Collection<Teacher> findAll(Pagination pagination, List<Condition> conditionList_json_str, Connection connection) throws SQLException {
        Collection<Teacher> desiredTeachers = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM Teacher");
        if (conditionList_json_str != null) {
            String whereClause = Condition.toWhereClause(conditionList_json_str);
            sql_select.append(whereClause);
        }
        int totalNum = TeacherDao.getInstance().count(connection);
        if (pagination != null) {
            sql_select.append(pagination.toLimitClause(totalNum)).append(" ");
        }
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = preparedStatement.executeQuery();
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String name = resultSet_select.getString("name");
            String no = resultSet_select.getString("no");
            String sex = resultSet_select.getString("sex");
            String title = resultSet_select.getString("title");
            Integer age = resultSet_select.getInt("age");
            Teacher desiredTeacher = new Teacher(id, name, no, sex, age, title);
            desiredTeachers.add(desiredTeacher);
        }

        JdbcHelper.close(resultSet_select, preparedStatement);
        return desiredTeachers;
    }

    public Teacher find(Integer id, Connection connection) throws SQLException {
        Teacher desiredTeacher = null;
        String sql_select_id = "SELECT * FROM teacher WHERE id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if(resultSet_select_id.next()) {
            Integer id_teacher = resultSet_select_id.getInt ("id");
            String name = resultSet_select_id.getString("name");
            String no = resultSet_select_id.getString("no");
            String sex = resultSet_select_id.getString ("sex");
            String title = resultSet_select_id.getString ("title");
            Integer age = resultSet_select_id.getInt ("age");
            desiredTeacher = new Teacher(id_teacher,name,no,sex,age,title);
        }
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredTeacher;
    }

    /*
     *修改方法
     *修改数据库中的教师记录
     */
    public boolean update(Teacher teacher,Connection connection) throws SQLException {
        String update_sql = "UPDATE teacher SET name = ?,no = ?,sex = ?,title = ?,age = ?" + " WHERE id = ?";
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        pstmt.setString(1,teacher.getName());
        pstmt.setString(2,teacher.getNo());
        pstmt.setString (3,teacher.getSex ());
        pstmt.setString (4,teacher.getTitle ());
        pstmt.setInt(5,teacher.getAge ());
        pstmt.setInt (6,teacher.getId ());
        int affectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);
        return affectedRow>0;
    }

    /**
     * 由于Teacher扩展了Actor，前者对象id取后者的id的值，故参数teacher是有id的
     * 用于事务。调用本方法的Service，在调用本方法后，还会调用UserDao.add方法
     * 两次对Dao的调用要保证是同一个Connection对象，才能将它们设置在同一事务中
     * @param teacher    要增加到数据库表中的Teacher对象
     * @param connection 来自Service的Connection对象
     * @throws SQLException
     */
    public Boolean add(Teacher teacher, Connection connection) throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement preparedStatement =
                connection.prepareStatement(
                        "INSERT INTO teacher " +
                                " (id,no,sex,name,age,title)" +
                                " VALUES (?,?,?,?,?,?)");
        preparedStatement.setInt(1, teacher.getId());
        preparedStatement.setString(2, teacher.getNo ());
        preparedStatement.setString(3, teacher.getSex ());
        preparedStatement.setString (4, teacher.getName ());
        preparedStatement.setInt(5, teacher.getAge ());
        preparedStatement.setString (6, teacher.getTitle ());
        int affectedRowNum =preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return affectedRowNum == 1;
    }

    public boolean delete(Integer id, Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM teacher WHERE id=?");
        preparedStatement.setInt(1, id);
        System.out.println(id);
        int affectedRow = preparedStatement.executeUpdate();
        System.out.println(affectedRow);
        JdbcHelper.close(preparedStatement, null);
        return affectedRow > 0;
    }

    private int count(Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Teacher");
        ResultSet resultSet = preparedStatement.executeQuery();
        int r = 0;
        while (resultSet.next()) {
            r++;
        }
        return r;
    }


}
